Imports System.Collections
Imports System.Collections.Specialized
Imports System.Security.Cryptography
Imports System.Text
Imports System.Configuration
Imports System.Configuration.Provider
Imports nsDataProvider = KDESolutions.Providers.DataProvider

Public Class DBLookupValueProvider
    Inherits LookupValueProvider

    Private _AppId, _DBType, _DBServer, _DBUser, _DBPassword As String
    Private _DBDatabaseName, _ConnectString, _TablePrefix As String
    Private _enableLogicalDeletes As Boolean

    Public Const cTrueValue As String = "Y"
    Public Const cFalsevalue As String = "N"

    Public Property TablePrefix() As String
        Get
            Return _TablePrefix
        End Get
        Set(ByVal value As String)
            _TablePrefix = value
        End Set
    End Property

    Public Property MyDBType() As String
        Get
            Return _DBType
        End Get
        Set(ByVal value As String)
            _DBType = value
        End Set
    End Property

    Public Overrides ReadOnly Property Name() As String
        Get
            Return "KDESolutions.Providers.LookupValues.DBLookupValueProvider"
        End Get
    End Property

    Public Overrides ReadOnly Property Description() As String
        Get
            Return "Provider for storing lookup values"
        End Get
    End Property

    Public Overrides Sub Initialize(ByVal name As String, ByVal config As NameValueCollection)
        If (config Is Nothing) Then
            Throw New ArgumentNullException("config")
        End If

        If (config("applicationId") Is Nothing) Or (String.IsNullOrEmpty("applicationId")) Then
            Dim newEx As New Exception("You must supply your applicationId from the Applications table!")
            Throw newEx
        Else
            _AppId = config("applicationId")
        End If

        If ((name Is Nothing) OrElse (name.Length = 0)) Then
            name = "LookupValueProvider"
        End If
        If String.IsNullOrEmpty(config("description")) Then
            config.Remove("description")
            config.Add("description", "KDESolutions LookupValue provider")
        End If

        If String.IsNullOrEmpty(config("tablePrefix")) Then
            config.Remove("tablePrefix")
            config.Add("tablePrefix", "Forums")
            _TablePrefix = "lookup"
        Else
            _TablePrefix = config("tablePrefix")
        End If

        If String.IsNullOrEmpty(config("enableLogicalDeletes")) Then
            config.Remove("enableLogicalDeletes")
            config.Add("enableLogicalDeletes", False)
            _enableLogicalDeletes = False
        Else
            _enableLogicalDeletes = CType(config("enableLogicalDeletes"), Boolean)
        End If

        'Start of config file settings for database connection ******
        If String.IsNullOrEmpty("dataProviderGroup") Then
            Throw New ArgumentNullException("You must provide a DataProviderGroup")
        Else
            Dim DataProviderGroup As KDESolutions.Providers.DataProvider.DataProviderConfiguration = _
            CType(ConfigurationManager.GetSection(config("dataProviderGroup")), KDESolutions.Providers.DataProvider.DataProviderConfiguration)
            If String.IsNullOrEmpty(DataProviderGroup.DBType) = False Then
                _DBType = DataProviderGroup.DBType
                Me.MyDBType = DataProviderGroup.DBType
            End If
            If String.IsNullOrEmpty(DataProviderGroup.DBServer) = False Then
                _DBServer = DataProviderGroup.DBServer
            End If
            If String.IsNullOrEmpty(DataProviderGroup.DBUser) = False Then
                _DBUser = DataProviderGroup.DBUser
            End If
            If String.IsNullOrEmpty(DataProviderGroup.DBPassword) = False Then
                _DBPassword = DataProviderGroup.DBPassword
            End If
            If String.IsNullOrEmpty(DataProviderGroup.DBDatabaseName) = False Then
                _DBDatabaseName = DataProviderGroup.DBDatabaseName
            End If
            If String.IsNullOrEmpty(DataProviderGroup.ConnectString) = False Then
                _ConnectString = DataProviderGroup.ConnectString
            End If
        End If

        MyBase.Initialize(name, config)
        config.Remove("dataProviderGroup")
        config.Remove("productKey")
        config.Remove("activationDate")
        config.Remove("tablePrefix")
        config.Remove("applicationId")
        config.Remove("enableLogicalDeletes")
        If (config.Count > 0) Then
            Dim attribUnrecognized As String = config.GetKey(0)
            If Not String.IsNullOrEmpty(attribUnrecognized) Then
                Throw New ProviderException("Provider unrecognized attribute, " & attribUnrecognized)
            End If
        End If
    End Sub

    Public Overrides Function GetLookupCategoriesForReOrder() As Generic.List(Of KDESolutions.Providers.LookupValues.LookupBindingCategories)
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Try
            Dim sSQL As String
            sSQL = "SELECT CategoryId, SortOrder, CategoryShortDesc FROM " & _TablePrefix & "_Category WHERE ApplicationId = ?"
            DAP.AddParameter("@ApplicationId", _AppId, DbType.String)
            If _enableLogicalDeletes Then
                sSQL = sSQL & " AND LogicalDeleteInd = ?"
                DAP.AddParameter("@LogicalDeleteInd", cFalsevalue, DbType.String)
            End If
            sSQL = sSQL & " ORDER BY SortOrder ASC"
            Dim result As Generic.List(Of Common.DbDataRecord)
            result = DAP.ExecuteReader(sSQL)
            Dim returnValue As New Generic.List(Of KDESolutions.Providers.LookupValues.LookupBindingCategories)
            For Each resultItem As Common.DbDataRecord In result
                Dim newCategory As New KDESolutions.Providers.LookupValues.LookupBindingCategories
                newCategory.CategoryId = resultItem.Item("CategoryId")
                newCategory.ShortDesc = resultItem.Item("CategoryShortDesc")
                newCategory.SortOrder = resultItem.Item("SortOrder")
                returnValue.Add(newCategory)
            Next
            Return returnValue
        Finally
            DAP.Dispose()
        End Try
    End Function

    Public Overrides Function GetLookupValuesForReOrder() As Generic.List(Of KDESolutions.Providers.LookupValues.LookupBindingValues)
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Try
            Dim sSQL As String
            sSQL = "SELECT ValueId, SortOrder, ValueShortDesc FROM " & _TablePrefix & "_Values WHERE ApplicationId = ?"
            DAP.AddParameter("@ApplicationId", _AppId, DbType.String)
            If _enableLogicalDeletes Then
                sSQL = sSQL = " AND LogicalDeleteInd = ?"
                DAP.AddParameter("@LogicalDeleteInd", cFalsevalue, DbType.String)
            End If
            sSQL = sSQL & " ORDER BY SortOrder ASC"
            Dim result As Generic.List(Of Common.DbDataRecord)
            result = DAP.ExecuteReader(sSQL)
            Dim returnValue As New Generic.List(Of KDESolutions.Providers.LookupValues.LookupBindingValues)
            For Each resultItem As Common.DbDataRecord In result
                Dim newValue As New KDESolutions.Providers.LookupValues.LookupBindingValues
                newValue.ValueId = resultItem.Item("ValueId")
                newValue.ShortDesc = resultItem.Item("ValueShortDesc")
                newValue.SortOrder = resultItem.Item("SortOrder")
                returnValue.Add(newValue)
            Next
            Return returnValue
        Finally
            DAP.Dispose()
        End Try
    End Function


    Public Overrides Sub ReOrderLookupCategory(ByVal CategoryId As String, ByVal SortOrder As Integer, ByVal ShortDesc As String)
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Try
            Dim sSQL As String
            sSQL = "UPDATE " & _TablePrefix & "_Category SET SortOrder = ? WHERE CategoryId = ?"
            DAP.AddParameter("@SortOrder", SortOrder, DbType.String)
            DAP.AddParameter("@CategoryId", CategoryId, DbType.String)
            DAP.ExecuteNonQuery(sSQL)
        Finally
            DAP.Dispose()
        End Try
    End Sub

    Public Overrides Sub ReOrderLookupValue(ByVal ValueId As String, ByVal SortOrder As Integer, ByVal ShortDesc As String)
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Try
            Dim sSQL As String
            sSQL = "UPDATE " & _TablePrefix & "_Values SET SortOrder = ? WHERE ValueId = ?"
            DAP.AddParameter("@SortOrder", SortOrder, DbType.String)
            DAP.AddParameter("@CategoryId", ValueId, DbType.String)
            DAP.ExecuteNonQuery(sSQL)
        Finally
            DAP.Dispose()
        End Try
    End Sub

    Public Overrides Function createLookupCategory(ByVal ShortDesc As String, ByVal LongDesc As String, ByVal CurrentUser As String) As String
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Try
            Dim maxSortOrder As Integer = 0
            Dim sSQL As String
            If _enableLogicalDeletes Then
                sSQL = "SELECT SortOrder FROm lookup_Category WHERE ApplicationId = ? AND LogicalDeleteInd = '" & cFalsevalue & "' Order By SortOrder DESC"
                DAP.AddParameter("@ApplicationId", _AppId, DbType.String)
            Else
                sSQL = "SELECT SortOrder FROM lookup_Category WHERE ApplicationId = ? Order By SortOrder DESC"
                DAP.AddParameter("@ApplicationId", _AppId, DbType.String)
            End If
            Dim result As Object = DAP.ExecuteScalar(sSQL)
            If CType(result, Integer) > Integer.MinValue Then
                maxSortOrder = CType(result, Integer)
            End If

            sSQL = "INSERT INTO " & _TablePrefix & "_Category(ApplicationId,CategoryId,CategoryShortDesc," & _
            "CategoryLongDesc,CreateDate,CreatedBy,LastUpdatedDate,LastUpdatedBy,SortOrder"
            If _enableLogicalDeletes Then
                sSQL = sSQL & ",LogicalDeleteInd) "
            Else
                sSQL = sSQL & ") "
            End If
            sSQL = sSQL & "VALUES(?,?,?,?,?,?,?,?,?"
            If _enableLogicalDeletes Then
                sSQL = sSQL & ",?) "
            Else
                sSQL = sSQL & ") "
            End If
            Dim strNewGuid As String = System.Guid.NewGuid.ToString
            DAP.AddParameter("@ApplicationId", _AppId, DbType.String)
            DAP.AddParameter("@CategoryId", strNewGuid, DbType.String)
            DAP.AddParameter("@CategoryShortDesc", ShortDesc, DbType.String)
            DAP.AddParameter("@CategoryLongDesc", LongDesc, DbType.String)
            DAP.AddParameter("@CreateDate", Now.ToUniversalTime, DbType.DateTime)
            DAP.AddParameter("@CreatedBy", CurrentUser, DbType.String)
            DAP.AddParameter("@LastUpcatedDate", Now.ToUniversalTime, DbType.DateTime)
            DAP.AddParameter("@LastUpdatedBy", CurrentUser, DbType.String)
            DAP.AddParameter("@SortOrder", maxSortOrder + 1, DbType.Int64)
            If _enableLogicalDeletes Then
                DAP.AddParameter("@LogicalDeleteInd", cFalsevalue, DbType.String)
            End If
            DAP.ExecuteNonQuery(sSQL)
            Return strNewGuid
        Catch ex As Exception
            Throw ex
        Finally
            DAP.Dispose()
        End Try
    End Function

    Public Overrides Function createLookupValue(ByVal CategoryId As String, ByVal ValueCode As String, ByVal ShortDesc As String, ByVal LongDesc As String, ByVal CurrentUser As String, Optional ByVal SortOrder As Integer = 0, Optional ByVal ParentId As String = "") As String
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Try
            Dim sSQL As String
            sSQL = "INSERT INTO " & _TablePrefix & "_Values(ApplicationId,ValueId,CategoryId,ValueCode," & _
            "ValueShortDesc,ValueLongDesc,ValueParentId,SortOrder,CreateDate,CreatedBy,LastUpdatedDate," & _
            "LastUpdatedBy"
            If _enableLogicalDeletes Then
                sSQL = sSQL & ",LogicalDeleteInd) "
            Else
                sSQL = sSQL & ") "
            End If
            sSQL = sSQL & "VALUES(?,?,?,?,?,?,?,?,?,?,?,?"
            If _enableLogicalDeletes Then
                sSQL = sSQL & ",?) "
            Else
                sSQL = sSQL & ") "
            End If
            Dim strNewGuid As String = System.Guid.NewGuid.ToString
            DAP.AddParameter("@ApplicationId", _AppId, DbType.String)
            DAP.AddParameter("@ValueId", strNewGuid, DbType.String)
            DAP.AddParameter("@CategoryId", CategoryId, DbType.String)
            DAP.AddParameter("@ValueCode", ValueCode, DbType.String)
            DAP.AddParameter("@ValueShortDesc", ShortDesc, DbType.String)
            DAP.AddParameter("@ValueLongDesc", LongDesc, DbType.String)
            DAP.AddParameter("@ValueParentId", ParentId, DbType.String)
            DAP.AddParameter("@SortOrder", SortOrder, DbType.Int64)
            DAP.AddParameter("@CreateDate", Now.ToUniversalTime, DbType.DateTime)
            DAP.AddParameter("@CreatedBy", CurrentUser, DbType.String)
            DAP.AddParameter("@LastUpcatedDate", Now.ToUniversalTime, DbType.DateTime)
            DAP.AddParameter("@LastUpdatedBy", CurrentUser, DbType.String)
            If _enableLogicalDeletes Then
                DAP.AddParameter("@LogicalDeleteInd", cFalsevalue, DbType.String)
            End If
            DAP.ExecuteNonQuery(sSQL)
            Return strNewGuid
        Finally
            DAP.Dispose()
        End Try
    End Function

    Public Overrides Sub deleteLookupCategory(ByVal CategoryId As String, ByVal CurrentUser As String)
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Try
            Dim sSQL As String
            If _enableLogicalDeletes Then
                sSQL = "UPDATE " & _TablePrefix & "_Category SET LogicalDeleteInd = ? WHERE ApplicationId = ? " & _
                "CategoryId = ?"
                DAP.AddParameter("@LogicalDeleteInd", cTrueValue, DbType.String)
            Else
                sSQL = "DELETE FROM " & _TablePrefix & "_Category WHERE ApplicationId = ? AND CategoryId = ?"
            End If
            DAP.AddParameter("@ApplicationId", _AppId, DbType.String)
            DAP.AddParameter("@CategoryId", CategoryId, DbType.String)
            DAP.ExecuteNonQuery(sSQL)
        Finally
            DAP.Dispose()
        End Try
    End Sub

    Public Overrides Sub deleteLookupValue(ByVal ValueId As String, ByVal CurrentUser As String)
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Try
            Dim sSQL As String
            If _enableLogicalDeletes Then
                sSQL = "UPDATE " & _TablePrefix & "_Values SET LogicalDeleteInd = ? WHERE ApplicationId = ? " & _
                "ValueId = ?"
                DAP.AddParameter("@LogicalDeleteInd", cTrueValue, DbType.String)
            Else
                sSQL = "DELETE FROM " & _TablePrefix & "_Values WHERE ApplicationId = ? AND ValueId = ?"
            End If
            DAP.AddParameter("@ApplicationId", _AppId, DbType.String)
            DAP.AddParameter("@ValueId", ValueId, DbType.String)
            DAP.ExecuteNonQuery(sSQL)
        Finally
            DAP.Dispose()
        End Try
    End Sub

    Public Overloads Overrides Function getLookupCategories() As System.Collections.Generic.List(Of LookupCategoryDetails)
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Try
            Dim returnValue As New Generic.List(Of LookupCategoryDetails)
            Dim sSQL As String
            sSQL = "SELECT CategoryId, CategoryShortDesc, CategoryLongDesc, SortOrder, CreateDate, " & _
            "CreatedBy, LastUpdatedDate, LastUpdatedBy FROM " & _TablePrefix & "_Category " & _
            "WHERE ApplicationId = ? "
            If _enableLogicalDeletes Then
                sSQL = sSQL & "AND LogicalDeleteInd = 'N' ORDER BY SortOrder, CategoryShortDesc ASC"
            Else
                sSQL = sSQL & "ORDER BY SortOrder, CategoryShortDesc ASC"
            End If
            DAP.AddParameter("@ApplicationId", _AppId, DbType.String)
            Dim result As Generic.List(Of Common.DbDataRecord)
            result = DAP.ExecuteReader(sSQL)
            For Each resultItem As Common.DbDataRecord In result
                Dim newCategoryItem As New LookupCategoryDetails
                newCategoryItem.CategoryId = resultItem.Item("CategoryId")
                newCategoryItem.ShortDesc = resultItem.Item("CategoryShortDesc")
                newCategoryItem.LongDesc = resultItem.Item("CategoryLongDesc")
                If IsDBNull(resultItem.Item("SortOrder")) = False Then
                    newCategoryItem.SortOrder = resultItem.Item("SortOrder")
                End If
                newCategoryItem.CreateDate = resultItem.Item("CreateDate")
                newCategoryItem.CreatedBy = resultItem.Item("CreatedBy")
                newCategoryItem.LastUpdatedDate = resultItem.Item("LastUpdatedDate")
                newCategoryItem.LastUpdatedBy = resultItem.Item("LastUpdatedBy")
                returnValue.Add(newCategoryItem)
            Next
            Return returnValue
        Finally
            DAP.Dispose()
        End Try
    End Function

    Public Overloads Overrides Function getLookupCategories(ByVal SortExpression As String, ByVal SortOrder As KDESolutions.Providers.DataProvider.SortOrder) As System.Collections.Generic.List(Of LookupCategoryDetails)
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim strSortColumn As String = String.Empty
        Dim strOrderBy As String = String.Empty
        Select Case SortExpression
            Case Is = "ShortDesc"
                strSortColumn = "CategoryShortDesc"
            Case Is = "LongDesc"
                strSortColumn = "CategoryLongDesc"
            Case Is = "SortOrder"
                strSortColumn = "SortOrder"
            Case Is = "CreateDate"
                strSortColumn = "CreateDate"
            Case Is = "CreatedBy"
                strSortColumn = "CreatedBy"
            Case Is = "LastUpdatedBy"
                strSortColumn = "LastUpdatedBy"
            Case Is = "LastUpdatedDate"
                strSortColumn = "LastUpdatedDate"
            Case Else
                Throw New Exception("You have specified an invalid property to sort by, please verify the SortExpression is set to a valid property of the KDESolutions.Providers.LookupValues.DataTypes.LookupCategoryDetails object.")
        End Select

        Select Case SortOrder
            Case DataProvider.SortOrder.Ascending
                strOrderBy = strSortColumn & " ASC"
            Case DataProvider.SortOrder.Descending
                strOrderBy = strSortColumn & " ASC"
        End Select
        Try
            Dim returnValue As New Generic.List(Of LookupCategoryDetails)
            Dim sSQL As String
            sSQL = "SELECT CategoryId, CategoryShortDesc, CategoryLongDesc, CreateDate, " & _
            "CreatedBy, LastUpdatedDate, LastUpdatedBy FROM " & _TablePrefix & "_Category " & _
            "WHERE ApplicationId = ? "
            If _enableLogicalDeletes Then
                sSQL = sSQL & "AND LogicalDeleteInd = 'N' "
            End If
            sSQL = sSQL & strOrderBy
            DAP.AddParameter("@ApplicationId", _AppId, DbType.String)
            Dim result As Generic.List(Of Common.DbDataRecord)
            result = DAP.ExecuteReader(sSQL)
            For Each resultItem As Common.DbDataRecord In result
                Dim newCategoryItem As New LookupCategoryDetails
                newCategoryItem.CategoryId = resultItem.Item("CategoryId")
                newCategoryItem.ShortDesc = resultItem.Item("CategoryShortDesc")
                newCategoryItem.LongDesc = resultItem.Item("CategoryLongDesc")
                newCategoryItem.CreateDate = resultItem.Item("CreateDate")
                newCategoryItem.CreatedBy = resultItem.Item("CreatedBy")
                newCategoryItem.LastUpdatedDate = resultItem.Item("LastUpdatedDate")
                newCategoryItem.LastUpdatedBy = resultItem.Item("LastUpdatedBy")
                returnValue.Add(newCategoryItem)
            Next
            Return returnValue
        Finally
            DAP.Dispose()
        End Try
    End Function

    Public Overloads Overrides Function getLookupValues(ByVal CategoryId As String, ByVal SortExpression As String, ByVal SortOrder As KDESolutions.Providers.DataProvider.SortOrder) As System.Collections.Generic.List(Of LookupValueDetails)
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Dim strSortColumn As String = String.Empty
        Dim strOrderBy As String = String.Empty
        Select Case SortExpression
            Case Is = "Value"
                strSortColumn = "ValueCode"
            Case Is = "ShortDesc"
                strSortColumn = "ValueShortDesc"
            Case Is = "LongDesc"
                strSortColumn = "ValueLongDesc"
            Case Is = "SortOrder"
                strSortColumn = "SortOrder"
            Case Is = "CreateDate"
                strSortColumn = "CreateDate"
            Case Is = "CreatedBy"
                strSortColumn = "CreatedBy"
            Case Is = "LastUpdatedBy"
                strSortColumn = "LastUpdatedBy"
            Case Is = "LastUpdatedDate"
                strSortColumn = "LastUpdatedDate"
            Case Else
                Throw New Exception("You have specified an invalid property to sort by, please verify the SortExpression is set to a valid property of the KDESolutions.Providers.LookupValues.DataTypes.LookupValueDetails object.")
        End Select

        Select Case SortOrder
            Case DataProvider.SortOrder.Ascending
                strOrderBy = strSortColumn & " ASC"
            Case DataProvider.SortOrder.Descending
                strOrderBy = strSortColumn & " ASC"
        End Select
        Try
            Dim returnValue As New Generic.List(Of LookupValueDetails)
            Dim sSQL As String
            sSQL = "SELECT ValueId, CategoryId, ValueCode, ValueShortDesc, ValueLongDesc, ValueParentId, " & _
            "SortOrder, CreateDate, " & _
            "CreatedBy, LastUpdatedDate, LastUpdatedBy FROM " & _TablePrefix & "_Values " & _
            "WHERE ApplicationId = ? "
            If _enableLogicalDeletes Then
                sSQL = sSQL & "AND LogicalDeleteInd = 'N' "
            End If
            sSQL = sSQL & strOrderBy
            DAP.AddParameter("@ApplicationId", _AppId, DbType.String)
            Dim result As Generic.List(Of Common.DbDataRecord)
            result = DAP.ExecuteReader(sSQL)
            For Each resultItem As Common.DbDataRecord In result
                Dim newValueItem As New LookupValueDetails
                newValueItem.ValueId = resultItem.Item("ValueId")
                newValueItem.CategoryId = resultItem.Item("CategoryId")
                newValueItem.Value = resultItem.Item("ValueCode")
                newValueItem.ShortDesc = resultItem.Item("ValueShortDesc")
                newValueItem.LongDesc = resultItem.Item("ValueLongDesc")
                newValueItem.ParentId = resultItem.Item("ValueParentId")
                newValueItem.SortOrder = resultItem.Item("SortOrder")
                newValueItem.CreateDate = resultItem.Item("CreateDate")
                newValueItem.CreatedBy = resultItem.Item("CreatedBy")
                newValueItem.LastUpdatedDate = resultItem.Item("LastUpdatedDate")
                newValueItem.LastUpdatedBy = resultItem.Item("LastUpdatedBy")
                returnValue.Add(newValueItem)
            Next
            Return returnValue
        Finally
            DAP.Dispose()
        End Try
    End Function
    Public Overrides Function getLookupSubValues(ByVal ValueParentId As String) As System.Collections.Generic.List(Of LookupValueDetails)
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Try
            Dim returnValue As New Generic.List(Of LookupValueDetails)
            Dim sSQL As String
            sSQL = "SELECT ValueId, CategoryId, ValueCode, ValueShortDesc, ValueLongDesc, ValueParentId, " & _
            "SortOrder, CreateDate, " & _
            "CreatedBy, LastUpdatedDate, LastUpdatedBy FROM " & _TablePrefix & "_Values " & _
            "WHERE ApplicationId = ? AND ValueParentId = ? "
            If _enableLogicalDeletes Then
                sSQL = sSQL & "AND LogicalDeleteInd = 'N' ORDER BY SortOrder, ValueShortDesc ASC"
            Else
                sSQL = sSQL & "ORDER BY SortOrder, ValueShortDesc ASC"
            End If
            DAP.AddParameter("@ApplicationId", _AppId, DbType.String)
            DAP.AddParameter("@ValueParentId", ValueParentId, DbType.String)

            Dim result As Generic.List(Of Common.DbDataRecord)
            result = DAP.ExecuteReader(sSQL)
            For Each resultItem As Common.DbDataRecord In result
                Dim newValueItem As New LookupValueDetails
                newValueItem.ValueId = resultItem.Item("CategoryId")
                newValueItem.Value = resultItem.Item("ValueCode")
                newValueItem.ShortDesc = resultItem.Item("ValueShortDesc")
                newValueItem.LongDesc = resultItem.Item("ValueLongDesc")
                newValueItem.ParentId = resultItem.Item("ValueParentId")
                newValueItem.SortOrder = resultItem.Item("SortOrder")
                newValueItem.CreateDate = resultItem.Item("CreateDate")
                newValueItem.CreatedBy = resultItem.Item("CreatedBy")
                newValueItem.LastUpdatedDate = resultItem.Item("LastUpdatedDate")
                newValueItem.LastUpdatedBy = resultItem.Item("LastUpdatedBy")
                returnValue.Add(newValueItem)
            Next
            Return returnValue
        Finally
            DAP.Dispose()
        End Try
    End Function

    Public Overrides Function getLookupValues(ByVal CategoryId As String) As System.Collections.Generic.List(Of LookupValueDetails)
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Try
            Dim returnValue As New Generic.List(Of LookupValueDetails)
            Dim sSQL As String
            sSQL = "SELECT ValueId, CategoryId, ValueCode, ValueShortDesc, ValueLongDesc, ValueParentId, " & _
            "SortOrder, CreateDate, " & _
            "CreatedBy, LastUpdatedDate, LastUpdatedBy FROM " & _TablePrefix & "_Values " & _
            "WHERE ApplicationId = ? AND CategoryId = ? "
            If _enableLogicalDeletes Then
                sSQL = sSQL & "AND LogicalDeleteInd = 'N' ORDER BY SortOrder, ValueShortDesc ASC"
            Else
                sSQL = sSQL & "ORDER BY SortOrder, ValueShortDesc ASC"
            End If
            DAP.AddParameter("@ApplicationId", _AppId, DbType.String)
            DAP.AddParameter("@CategoryId", CategoryId, DbType.String)

            Dim result As Generic.List(Of Common.DbDataRecord)
            result = DAP.ExecuteReader(sSQL)
            For Each resultItem As Common.DbDataRecord In result
                Dim newValueItem As New LookupValueDetails
                newValueItem.ValueId = resultItem.Item("CategoryId")
                newValueItem.Value = resultItem.Item("ValueCode")
                newValueItem.ShortDesc = resultItem.Item("ValueShortDesc")
                newValueItem.LongDesc = resultItem.Item("ValueLongDesc")
                newValueItem.ParentId = resultItem.Item("ValueParentId")
                newValueItem.SortOrder = resultItem.Item("SortOrder")
                newValueItem.CreateDate = resultItem.Item("CreateDate")
                newValueItem.CreatedBy = resultItem.Item("CreatedBy")
                newValueItem.LastUpdatedDate = resultItem.Item("LastUpdatedDate")
                newValueItem.LastUpdatedBy = resultItem.Item("LastUpdatedBy")
                returnValue.Add(newValueItem)
            Next
            Return returnValue
        Finally
            DAP.Dispose()
        End Try
    End Function

    Public Overrides Function searchLookupCategories(ByVal SearchCollection As System.Collections.Generic.List(Of DataProvider.SearchCriteria)) As System.Collections.Generic.List(Of LookupCategoryDetails)
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Try
            Dim returnValue As New Generic.List(Of LookupCategoryDetails)
            Dim sSQL As String
            sSQL = "SELECT CategoryId, CategoryShortDesc, CategoryLongDesc, SortOrder, CreateDate, " & _
            "CreatedBy, LastUpdatedDate, LastUpdatedBy FROM " & _TablePrefix & "_Category " & _
            "WHERE ApplicationId = ? "
            Dim strAndClause As String = String.Empty
            'TODO: Add some better error handling into this for searches
            For Each SearchItem As KDESolutions.Providers.DataProvider.SearchCriteria In SearchCollection
                Select Case SearchItem.SearchProperty
                    Case Is = "ShortDesc"
                        Select Case SearchItem.SearchOperator
                            Case DataProvider.SearchOperators.Contains
                                strAndClause = nsDataProvider.SearchHelpers.GetDBWhereClause("CategoryShortDesc", SearchItem.SearchOperator, SearchItem.SearchValue)
                            Case DataProvider.SearchOperators.EndsWith
                                strAndClause = nsDataProvider.SearchHelpers.GetDBWhereClause("CategoryShortDesc", SearchItem.SearchOperator, SearchItem.SearchValue)
                            Case DataProvider.SearchOperators.StartsWith
                                strAndClause = nsDataProvider.SearchHelpers.GetDBWhereClause("CategoryShortDesc", SearchItem.SearchOperator, SearchItem.SearchValue)
                            Case DataProvider.SearchOperators.Equals
                                strAndClause = nsDataProvider.SearchHelpers.GetDBWhereClause("CategoryShortDesc", SearchItem.SearchOperator, SearchItem.SearchValue)
                            Case Else
                                Throw New Exception("The search operator " & SearchItem.SearchOperator.ToString & " is invalid for property " & SearchItem.SearchProperty)
                        End Select
                    Case Is = "LongDesc"
                        Select Case SearchItem.SearchOperator
                            Case DataProvider.SearchOperators.Contains
                                strAndClause = nsDataProvider.SearchHelpers.GetDBWhereClause("CategoryLongDesc", SearchItem.SearchOperator, SearchItem.SearchValue)
                            Case DataProvider.SearchOperators.EndsWith
                                strAndClause = nsDataProvider.SearchHelpers.GetDBWhereClause("CategoryLongDesc", SearchItem.SearchOperator, SearchItem.SearchValue)
                            Case DataProvider.SearchOperators.StartsWith
                                strAndClause = nsDataProvider.SearchHelpers.GetDBWhereClause("CategoryLongDesc", SearchItem.SearchOperator, SearchItem.SearchValue)
                            Case DataProvider.SearchOperators.Equals
                                strAndClause = nsDataProvider.SearchHelpers.GetDBWhereClause("CategoryLongDesc", SearchItem.SearchOperator, SearchItem.SearchValue)
                            Case Else
                                Throw New Exception("The search operator " & SearchItem.SearchOperator.ToString & " is invalid for property " & SearchItem.SearchProperty)
                        End Select
                    Case Is = "SortOrder"
                        Select Case SearchItem.SearchOperator
                            Case DataProvider.SearchOperators.Equals
                                strAndClause = nsDataProvider.SearchHelpers.GetDBWhereClause("SortOrder", SearchItem.SearchOperator, SearchItem.SearchValue)
                            Case DataProvider.SearchOperators.GeaterThanOrEqualTo
                                strAndClause = nsDataProvider.SearchHelpers.GetDBWhereClause("SortOrder", SearchItem.SearchOperator, SearchItem.SearchValue)
                            Case DataProvider.SearchOperators.GreaterThan
                                strAndClause = nsDataProvider.SearchHelpers.GetDBWhereClause("SortOrder", SearchItem.SearchOperator, SearchItem.SearchValue)
                            Case DataProvider.SearchOperators.LessThan
                                strAndClause = nsDataProvider.SearchHelpers.GetDBWhereClause("SortOrder", SearchItem.SearchOperator, SearchItem.SearchValue)
                            Case DataProvider.SearchOperators.LessThanOrEqualTo
                                strAndClause = nsDataProvider.SearchHelpers.GetDBWhereClause("SortOrder", SearchItem.SearchOperator, SearchItem.SearchValue)
                            Case Else
                                Throw New Exception("The search operator " & SearchItem.SearchOperator.ToString & " is invalid for property " & SearchItem.SearchProperty)
                        End Select
                    Case Is = "CreateDate"
                        Select Case SearchItem.SearchOperator
                            Case DataProvider.SearchOperators.Equals
                                strAndClause = nsDataProvider.SearchHelpers.GetDBWhereClause("CreateDate", SearchItem.SearchOperator, SearchItem.SearchValue)
                            Case DataProvider.SearchOperators.GeaterThanOrEqualTo
                                strAndClause = nsDataProvider.SearchHelpers.GetDBWhereClause("CreateDate", SearchItem.SearchOperator, SearchItem.SearchValue)
                            Case DataProvider.SearchOperators.GreaterThan
                                strAndClause = nsDataProvider.SearchHelpers.GetDBWhereClause("CreateDate", SearchItem.SearchOperator, SearchItem.SearchValue)
                            Case DataProvider.SearchOperators.LessThan
                                strAndClause = nsDataProvider.SearchHelpers.GetDBWhereClause("CreateDate", SearchItem.SearchOperator, SearchItem.SearchValue)
                            Case DataProvider.SearchOperators.LessThanOrEqualTo
                                strAndClause = nsDataProvider.SearchHelpers.GetDBWhereClause("CreateDate", SearchItem.SearchOperator, SearchItem.SearchValue)
                            Case Else
                                Throw New Exception("The search operator " & SearchItem.SearchOperator.ToString & " is invalid for property " & SearchItem.SearchProperty)
                        End Select
                    Case Is = "CreatedBy"
                        Select Case SearchItem.SearchOperator
                            Case DataProvider.SearchOperators.Contains
                                strAndClause = nsDataProvider.SearchHelpers.GetDBWhereClause("CreatedBy", SearchItem.SearchOperator, SearchItem.SearchValue)
                            Case DataProvider.SearchOperators.EndsWith
                                strAndClause = nsDataProvider.SearchHelpers.GetDBWhereClause("CreatedBy", SearchItem.SearchOperator, SearchItem.SearchValue)
                            Case DataProvider.SearchOperators.StartsWith
                                strAndClause = nsDataProvider.SearchHelpers.GetDBWhereClause("CreatedBy", SearchItem.SearchOperator, SearchItem.SearchValue)
                            Case DataProvider.SearchOperators.Equals
                                strAndClause = nsDataProvider.SearchHelpers.GetDBWhereClause("CreatedBy", SearchItem.SearchOperator, SearchItem.SearchValue)
                            Case Else
                                Throw New Exception("The search operator " & SearchItem.SearchOperator.ToString & " is invalid for property " & SearchItem.SearchProperty)
                        End Select
                    Case Is = "LastUpdatedDate"
                        Select Case SearchItem.SearchOperator
                            Case DataProvider.SearchOperators.Equals
                                strAndClause = nsDataProvider.SearchHelpers.GetDBWhereClause("LastUpdatedDate", SearchItem.SearchOperator, SearchItem.SearchValue)
                            Case DataProvider.SearchOperators.GeaterThanOrEqualTo
                                strAndClause = nsDataProvider.SearchHelpers.GetDBWhereClause("LastUpdatedDate", SearchItem.SearchOperator, SearchItem.SearchValue)
                            Case DataProvider.SearchOperators.GreaterThan
                                strAndClause = nsDataProvider.SearchHelpers.GetDBWhereClause("LastUpdatedDate", SearchItem.SearchOperator, SearchItem.SearchValue)
                            Case DataProvider.SearchOperators.LessThan
                                strAndClause = nsDataProvider.SearchHelpers.GetDBWhereClause("LastUpdatedDate", SearchItem.SearchOperator, SearchItem.SearchValue)
                            Case DataProvider.SearchOperators.LessThanOrEqualTo
                                strAndClause = nsDataProvider.SearchHelpers.GetDBWhereClause("LastUpdatedDate", SearchItem.SearchOperator, SearchItem.SearchValue)
                            Case Else
                                Throw New Exception("The search operator " & SearchItem.SearchOperator.ToString & " is invalid for property " & SearchItem.SearchProperty)
                        End Select
                    Case Is = "LastUpdatedBy"
                        Select Case SearchItem.SearchOperator
                            Case DataProvider.SearchOperators.Contains
                                strAndClause = nsDataProvider.SearchHelpers.GetDBWhereClause("LastUpdatedBy", SearchItem.SearchOperator, SearchItem.SearchValue)
                            Case DataProvider.SearchOperators.EndsWith
                                strAndClause = nsDataProvider.SearchHelpers.GetDBWhereClause("LastUpdatedBy", SearchItem.SearchOperator, SearchItem.SearchValue)
                            Case DataProvider.SearchOperators.StartsWith
                                strAndClause = nsDataProvider.SearchHelpers.GetDBWhereClause("LastUpdatedBy", SearchItem.SearchOperator, SearchItem.SearchValue)
                            Case DataProvider.SearchOperators.Equals
                                strAndClause = nsDataProvider.SearchHelpers.GetDBWhereClause("LastUpdatedBy", SearchItem.SearchOperator, SearchItem.SearchValue)
                            Case Else
                                Throw New Exception("The search operator " & SearchItem.SearchOperator.ToString & " is invalid for property " & SearchItem.SearchProperty)
                        End Select
                    Case Else
                        Throw New Exception("You have specified an invalid property to search by, please verify the properties in your SearchCollection are set to a valid property of the KDESolutions.Providers.LookupValues.DataTypes.LookupCategoryDetails object.")
                End Select
                sSQL = sSQL & strAndClause
                strAndClause = String.Empty
            Next
            If _enableLogicalDeletes Then
                sSQL = sSQL & "AND LogicalDeleteInd = 'N' ORDER BY SortOrder, CategoryShortDesc ASC"
            Else
                sSQL = sSQL & "ORDER BY SortOrder, CategoryShortDesc ASC"
            End If
            DAP.AddParameter("@ApplicationId", _AppId, DbType.String)
            Dim result As Generic.List(Of Common.DbDataRecord)
            result = DAP.ExecuteReader(sSQL)
            For Each resultItem As Common.DbDataRecord In result
                Dim newCategoryItem As New LookupCategoryDetails
                newCategoryItem.CategoryId = resultItem.Item("CategoryId")
                newCategoryItem.ShortDesc = resultItem.Item("CategoryShortDesc")
                newCategoryItem.LongDesc = resultItem.Item("CategoryLongDesc")
                newCategoryItem.SortOrder = resultItem.Item("SortOrder")
                newCategoryItem.CreateDate = resultItem.Item("CreateDate")
                newCategoryItem.CreatedBy = resultItem.Item("CreatedBy")
                newCategoryItem.LastUpdatedDate = resultItem.Item("LastUpdatedDate")
                newCategoryItem.LastUpdatedBy = resultItem.Item("LastUpdatedBy")
                returnValue.Add(newCategoryItem)
            Next
            Return returnValue
        Finally
            DAP.Dispose()
        End Try
    End Function

    Public Overrides Function searchLookupValues(ByVal SearchCollection As System.Collections.Generic.List(Of DataProvider.SearchCriteria)) As System.Collections.Generic.List(Of LookupValueDetails)
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Try
            Dim returnValue As New Generic.List(Of LookupValueDetails)
            Dim sSQL As String
            sSQL = "SELECT ValueId, CategoryId, ValueShortDesc, ValueLongDesc, ValueParentId, SortOrder, CreateDate, " & _
            "CreatedBy, LastUpdatedDate, LastUpdatedBy FROM " & _TablePrefix & "_Category " & _
            "WHERE ApplicationId = ? "
            Dim strAndClause As String = String.Empty
            'TODO: Add some better error handling into this for searches
            For Each SearchItem As KDESolutions.Providers.DataProvider.SearchCriteria In SearchCollection
                Select Case SearchItem.SearchProperty
                    Case Is = "ShortDesc"
                        Select Case SearchItem.SearchOperator
                            Case DataProvider.SearchOperators.Contains
                                strAndClause = nsDataProvider.SearchHelpers.GetDBWhereClause("CategoryShortDesc", SearchItem.SearchOperator, SearchItem.SearchValue)
                            Case DataProvider.SearchOperators.EndsWith
                                strAndClause = nsDataProvider.SearchHelpers.GetDBWhereClause("CategoryShortDesc", SearchItem.SearchOperator, SearchItem.SearchValue)
                            Case DataProvider.SearchOperators.StartsWith
                                strAndClause = nsDataProvider.SearchHelpers.GetDBWhereClause("CategoryShortDesc", SearchItem.SearchOperator, SearchItem.SearchValue)
                            Case DataProvider.SearchOperators.Equals
                                strAndClause = nsDataProvider.SearchHelpers.GetDBWhereClause("CategoryShortDesc", SearchItem.SearchOperator, SearchItem.SearchValue)
                            Case Else
                                Throw New Exception("The search operator " & SearchItem.SearchOperator.ToString & " is invalid for property " & SearchItem.SearchProperty)
                        End Select
                    Case Is = "LongDesc"
                        Select Case SearchItem.SearchOperator
                            Case DataProvider.SearchOperators.Contains
                                strAndClause = nsDataProvider.SearchHelpers.GetDBWhereClause("CategoryLongDesc", SearchItem.SearchOperator, SearchItem.SearchValue)
                            Case DataProvider.SearchOperators.EndsWith
                                strAndClause = nsDataProvider.SearchHelpers.GetDBWhereClause("CategoryLongDesc", SearchItem.SearchOperator, SearchItem.SearchValue)
                            Case DataProvider.SearchOperators.StartsWith
                                strAndClause = nsDataProvider.SearchHelpers.GetDBWhereClause("CategoryLongDesc", SearchItem.SearchOperator, SearchItem.SearchValue)
                            Case DataProvider.SearchOperators.Equals
                                strAndClause = nsDataProvider.SearchHelpers.GetDBWhereClause("CategoryLongDesc", SearchItem.SearchOperator, SearchItem.SearchValue)
                            Case Else
                                Throw New Exception("The search operator " & SearchItem.SearchOperator.ToString & " is invalid for property " & SearchItem.SearchProperty)
                        End Select
                    Case Is = "SortOrder"
                        Select Case SearchItem.SearchOperator
                            Case DataProvider.SearchOperators.Equals
                                strAndClause = nsDataProvider.SearchHelpers.GetDBWhereClause("SortOrder", SearchItem.SearchOperator, SearchItem.SearchValue)
                            Case DataProvider.SearchOperators.GeaterThanOrEqualTo
                                strAndClause = nsDataProvider.SearchHelpers.GetDBWhereClause("SortOrder", SearchItem.SearchOperator, SearchItem.SearchValue)
                            Case DataProvider.SearchOperators.GreaterThan
                                strAndClause = nsDataProvider.SearchHelpers.GetDBWhereClause("SortOrder", SearchItem.SearchOperator, SearchItem.SearchValue)
                            Case DataProvider.SearchOperators.LessThan
                                strAndClause = nsDataProvider.SearchHelpers.GetDBWhereClause("SortOrder", SearchItem.SearchOperator, SearchItem.SearchValue)
                            Case DataProvider.SearchOperators.LessThanOrEqualTo
                                strAndClause = nsDataProvider.SearchHelpers.GetDBWhereClause("SortOrder", SearchItem.SearchOperator, SearchItem.SearchValue)
                            Case Else
                                Throw New Exception("The search operator " & SearchItem.SearchOperator.ToString & " is invalid for property " & SearchItem.SearchProperty)
                        End Select
                    Case Is = "CreateDate"
                        Select Case SearchItem.SearchOperator
                            Case DataProvider.SearchOperators.Equals
                                strAndClause = nsDataProvider.SearchHelpers.GetDBWhereClause("CreateDate", SearchItem.SearchOperator, SearchItem.SearchValue)
                            Case DataProvider.SearchOperators.GeaterThanOrEqualTo
                                strAndClause = nsDataProvider.SearchHelpers.GetDBWhereClause("CreateDate", SearchItem.SearchOperator, SearchItem.SearchValue)
                            Case DataProvider.SearchOperators.GreaterThan
                                strAndClause = nsDataProvider.SearchHelpers.GetDBWhereClause("CreateDate", SearchItem.SearchOperator, SearchItem.SearchValue)
                            Case DataProvider.SearchOperators.LessThan
                                strAndClause = nsDataProvider.SearchHelpers.GetDBWhereClause("CreateDate", SearchItem.SearchOperator, SearchItem.SearchValue)
                            Case DataProvider.SearchOperators.LessThanOrEqualTo
                                strAndClause = nsDataProvider.SearchHelpers.GetDBWhereClause("CreateDate", SearchItem.SearchOperator, SearchItem.SearchValue)
                            Case Else
                                Throw New Exception("The search operator " & SearchItem.SearchOperator.ToString & " is invalid for property " & SearchItem.SearchProperty)
                        End Select
                    Case Is = "CreatedBy"
                        Select Case SearchItem.SearchOperator
                            Case DataProvider.SearchOperators.Contains
                                strAndClause = nsDataProvider.SearchHelpers.GetDBWhereClause("CreatedBy", SearchItem.SearchOperator, SearchItem.SearchValue)
                            Case DataProvider.SearchOperators.EndsWith
                                strAndClause = nsDataProvider.SearchHelpers.GetDBWhereClause("CreatedBy", SearchItem.SearchOperator, SearchItem.SearchValue)
                            Case DataProvider.SearchOperators.StartsWith
                                strAndClause = nsDataProvider.SearchHelpers.GetDBWhereClause("CreatedBy", SearchItem.SearchOperator, SearchItem.SearchValue)
                            Case DataProvider.SearchOperators.Equals
                                strAndClause = nsDataProvider.SearchHelpers.GetDBWhereClause("CreatedBy", SearchItem.SearchOperator, SearchItem.SearchValue)
                            Case Else
                                Throw New Exception("The search operator " & SearchItem.SearchOperator.ToString & " is invalid for property " & SearchItem.SearchProperty)
                        End Select
                    Case Is = "LastUpdatedDate"
                        Select Case SearchItem.SearchOperator
                            Case DataProvider.SearchOperators.Equals
                                strAndClause = nsDataProvider.SearchHelpers.GetDBWhereClause("LastUpdatedDate", SearchItem.SearchOperator, SearchItem.SearchValue)
                            Case DataProvider.SearchOperators.GeaterThanOrEqualTo
                                strAndClause = nsDataProvider.SearchHelpers.GetDBWhereClause("LastUpdatedDate", SearchItem.SearchOperator, SearchItem.SearchValue)
                            Case DataProvider.SearchOperators.GreaterThan
                                strAndClause = nsDataProvider.SearchHelpers.GetDBWhereClause("LastUpdatedDate", SearchItem.SearchOperator, SearchItem.SearchValue)
                            Case DataProvider.SearchOperators.LessThan
                                strAndClause = nsDataProvider.SearchHelpers.GetDBWhereClause("LastUpdatedDate", SearchItem.SearchOperator, SearchItem.SearchValue)
                            Case DataProvider.SearchOperators.LessThanOrEqualTo
                                strAndClause = nsDataProvider.SearchHelpers.GetDBWhereClause("LastUpdatedDate", SearchItem.SearchOperator, SearchItem.SearchValue)
                            Case Else
                                Throw New Exception("The search operator " & SearchItem.SearchOperator.ToString & " is invalid for property " & SearchItem.SearchProperty)
                        End Select
                    Case Is = "LastUpdatedBy"
                        Select Case SearchItem.SearchOperator
                            Case DataProvider.SearchOperators.Contains
                                strAndClause = nsDataProvider.SearchHelpers.GetDBWhereClause("LastUpdatedBy", SearchItem.SearchOperator, SearchItem.SearchValue)
                            Case DataProvider.SearchOperators.EndsWith
                                strAndClause = nsDataProvider.SearchHelpers.GetDBWhereClause("LastUpdatedBy", SearchItem.SearchOperator, SearchItem.SearchValue)
                            Case DataProvider.SearchOperators.StartsWith
                                strAndClause = nsDataProvider.SearchHelpers.GetDBWhereClause("LastUpdatedBy", SearchItem.SearchOperator, SearchItem.SearchValue)
                            Case DataProvider.SearchOperators.Equals
                                strAndClause = nsDataProvider.SearchHelpers.GetDBWhereClause("LastUpdatedBy", SearchItem.SearchOperator, SearchItem.SearchValue)
                            Case Else
                                Throw New Exception("The search operator " & SearchItem.SearchOperator.ToString & " is invalid for property " & SearchItem.SearchProperty)
                        End Select
                    Case Else
                        Throw New Exception("You have specified an invalid property to search by, please verify the properties in your SearchCollection are set to a valid property of the KDESolutions.Providers.LookupValues.DataTypes.LookupCategoryDetails object.")
                End Select
                sSQL = sSQL & strAndClause
                strAndClause = String.Empty
            Next
            If _enableLogicalDeletes Then
                sSQL = sSQL & "AND LogicalDeleteInd = 'N' ORDER BY SortOrder, CategoryShortDesc ASC"
            Else
                sSQL = sSQL & "ORDER BY SortOrder, CategoryShortDesc ASC"
            End If
            DAP.AddParameter("@ApplicationId", _AppId, DbType.String)
            Dim result As Generic.List(Of Common.DbDataRecord)
            result = DAP.ExecuteReader(sSQL)
            For Each resultItem As Common.DbDataRecord In result
                Dim newValueItem As New LookupValueDetails
                newValueItem.CategoryId = resultItem.Item("CategoryId")
                newValueItem.ShortDesc = resultItem.Item("CategoryShortDesc")
                newValueItem.LongDesc = resultItem.Item("CategoryLongDesc")
                newValueItem.SortOrder = resultItem.Item("SortOrder")
                newValueItem.CreateDate = resultItem.Item("CreateDate")
                newValueItem.CreatedBy = resultItem.Item("CreatedBy")
                newValueItem.LastUpdatedDate = resultItem.Item("LastUpdatedDate")
                newValueItem.LastUpdatedBy = resultItem.Item("LastUpdatedBy")
                returnValue.Add(newValueItem)
            Next
            Return returnValue
        Finally
            DAP.Dispose()
        End Try
    End Function

    Public Overrides Sub updateLookupCategory(ByVal CategoryId As String, ByVal ShortDesc As String, ByVal LongDesc As String, ByVal SortOrder As Integer, ByVal CurrentUser As String)
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Try
            Dim sSQL As String
            sSQL = "UPDATE " & _TablePrefix & "_Category SET CategoryShortDesc = ?, CategoryLongDesc = ?, SortOrder = ?, " & _
            "LastUpdatedDate = ?, LastUpdatedBy = ? WHERE CategoryId = ?"
            DAP.AddParameter("@CategoryShortDesc", ShortDesc, DbType.String)
            DAP.AddParameter("@CategoryLongDesc", LongDesc, DbType.String)
            DAP.AddParameter("@SortOrder", SortOrder, DbType.Int64)
            DAP.AddParameter("@LastUpdatedDate", Now().ToUniversalTime, DbType.DateTime)
            DAP.AddParameter("@LastUpdatedBy", CurrentUser, DbType.String)
            DAP.ExecuteNonQuery(sSQL)
        Finally
            DAP.Dispose()
        End Try
    End Sub

    Public Overrides Sub updateLookupValue(ByVal ValueId As String, ByVal CategoryId As String, ByVal ValueCode As String, ByVal ShortDesc As String, ByVal LongDesc As String, ByVal CurrentUser As String, Optional ByVal ParentId As String = "", Optional ByVal SortOrder As Integer = 0)
        Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
        Try
            Dim sSQL As String
            sSQL = "UPDATE " & _TablePrefix & "_Values SET CategoryId = ?, ValueCode = ?, ValueShortDesc = ?, ValueLongDesc = ?, SortOrder = ?, ValueParentId = ?, " & _
            "LastUpdatedDate = ?, LastUpdatedBy = ? WHERE CategoryId = ?"
            DAP.AddParameter("@CategoryId", CategoryId, DbType.String)
            DAP.AddParameter("@ValueCode", ValueCode, DbType.String)
            DAP.AddParameter("@ValueShortDesc", ShortDesc, DbType.String)
            DAP.AddParameter("@ValueLongDesc", LongDesc, DbType.String)
            DAP.AddParameter("@SortOrder", SortOrder, DbType.Int64)
            DAP.AddParameter("@LastUpdatedDate", Now().ToUniversalTime, DbType.DateTime)
            DAP.AddParameter("@LastUpdatedBy", CurrentUser, DbType.String)
            DAP.ExecuteNonQuery(sSQL)
        Finally
            DAP.Dispose()
        End Try
    End Sub

    Private Function CBoolToYesNo(ByVal InputValue As Boolean) As String
        If InputValue = True Then
            Return "Y"
        Else
            Return "N"
        End If
    End Function

    Private Function CYesNoToBool(ByVal InputString As String) As Boolean
        If InputString.ToLower = "y" Then
            Return True
        Else
            Return False
        End If
    End Function
End Class
